set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* *******************************************************************
** DATE :
** Writer :
** Memo :
******************************************************************** */
CREATE PROCEDURE [dbo].[TSP_User_GetList]
(
	@IsTransaction	tinyint			= 0
,	@ErrorCode		int				= 0		OUTPUT
,	@ErrorMessage	nvarchar( 1000 )= ''	OUTPUT

,	@IsRemoved	tinyint

,	@strUserID_search varchar(50) = null
,	@strUserName_search nvarchar(50) = null

,	@n1GroupType_search tinyint = null
,	@IsLocalUser_search char(1) = null

,	@dtJoinDate_start_search datetime = null
,	@dtJoinDate_end_search datetime = null

,	@PageNo	int	= 1	
,	@PageSize	tinyint	= 10	
,	@CntRow	int	= NULL	OUTPUT
,	@CntTotal	int	= NULL	OUTPUT
)
as
Set NOCOUNT ON

-- Frk Default Value
DECLARE @FrkDateNow DateTime
DECLARE @FrkRowCount int
DECLARE @FrkTranCount int
DECLARE @FrkErrorCode int
DECLARE @FrkErrorMessage nvarchar( 1000 )
Set @FrkDateNow = GetDate()
set @FrkTranCount = @@TRANCOUNT
-- Frk Check


If ( @IsTransaction = 0 )
Begin
	If ( @FrkTranCount < 1 )
	Begin
		Set @FrkErrorCode = -1
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Else If ( @FrkTranCount > 1 )
	Begin
		Set @FrkErrorCode = -2
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
End
Else
Begin
	If ( @FrkTranCount <> 0 )
	Begin
		Set @FrkErrorCode = -3
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Begin Tran
End

-- Implementation

DECLARE @SkipPage INT
SET @SkipPage = ( ( @PageNo - 1 ) * @PageSize )

IF ( @IsLocalUser_search IS NOT NULL )
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		IsLocalUser = @IsLocalUser_search
	AND	IsRemoved = @IsRemoved
	AND	1 = CASE WHEN @strUserID_search IS NULL OR strUserID = @strUserID_search THEN 1 ELSE 0 END
	AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
			WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
			ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
			WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
			ELSE 0 END

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
			dbo.TB_User with ( nolock )
		WHERE
			IsLocalUser = @IsLocalUser_search
		AND	IsRemoved = @IsRemoved
		AND	1 = CASE WHEN @strUserID_search IS NULL OR strUserID = @strUserID_search THEN 1 ELSE 0 END
		AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
				WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
				ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
				WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
				ELSE 0 END
		ORDER BY
			IsRemoved DESC, IsLocalUser DESC, dtJoinDate DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate

			,	ROW_NUMBER() OVER ( ORDER BY
				IsRemoved DESC, IsLocalUser DESC, dtJoinDate DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				IsLocalUser = @IsLocalUser_search
			AND	IsRemoved = @IsRemoved
			AND	1 = CASE WHEN @strUserID_search IS NULL OR strUserID = @strUserID_search THEN 1 ELSE 0 END
			AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
					WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
					ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
					WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
					ELSE 0 END
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			IsRemoved DESC, IsLocalUser DESC, dtJoinDate DESC
	END
END
ELSE IF ( @strUserID_search IS NOT NULL )
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		strUserID LIKE @strUserID_search + '%'
	AND	IsRemoved = @IsRemoved
	AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
			WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
			ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
			WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
			ELSE 0 END

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate

		FROM
			dbo.TB_User with ( nolock )
		WHERE
			strUserID LIKE @strUserID_search + '%'
		AND	IsRemoved = @IsRemoved
		AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
				WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
				ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
				WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
				ELSE 0 END
		ORDER BY
			strUserID DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate

		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate

			,	ROW_NUMBER() OVER ( ORDER BY
				strUserID DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				strUserID LIKE @strUserID_search + '%'
			AND	IsRemoved = @IsRemoved
			AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
					WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
					ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
					WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
					ELSE 0 END
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			strUserID DESC
	END
END
ELSE IF ( @strUserName_search IS NOT NULL )
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		strUserName LIKE @strUserName_search + '%'
	AND	IsRemoved = @IsRemoved
	AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
			WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
			ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
			WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
			ELSE 0 END


	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate

		FROM
			dbo.TB_User with ( nolock )
		WHERE
			strUserName LIKE @strUserName_search + '%'
		AND	IsRemoved = @IsRemoved
		AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
				WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
				ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
				WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
				ELSE 0 END
		ORDER BY
			IsRemoved DESC, strUserName DESC, dtJoinDate DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate
			,	ROW_NUMBER() OVER ( ORDER BY
				IsRemoved DESC, strUserName DESC, dtJoinDate DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				strUserName LIKE @strUserName_search + '%'
			AND	IsRemoved = @IsRemoved
			AND	1 = CASE WHEN @n1GroupType_search IS NULL OR n1GroupType = @n1GroupType_search THEN 1 ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
					WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
					ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
					WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
					ELSE 0 END
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			IsRemoved DESC, strUserName DESC, dtJoinDate DESC
	END
END
ELSE IF ( @n1GroupType_search IS NOT NULL )
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		n1GroupType = @n1GroupType_search
	AND	IsRemoved = @IsRemoved
	AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
			WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
			ELSE 0 END
	AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
			WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
			ELSE 0 END

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
			dbo.TB_User with ( nolock )
		WHERE
			n1GroupType = @n1GroupType_search
		AND	IsRemoved = @IsRemoved
		AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
				WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
				ELSE 0 END
		AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
				WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
				ELSE 0 END
		ORDER BY
			IsRemoved DESC, n1GroupType DESC, dtJoinDate DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate
			,	ROW_NUMBER() OVER ( ORDER BY
				IsRemoved DESC, n1GroupType DESC, dtJoinDate DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				n1GroupType = @n1GroupType_search
			AND	IsRemoved = @IsRemoved
			AND	1 = CASE WHEN @dtJoinDate_start_search IS NULL THEN 1
					WHEN dtJoinDate <= @dtJoinDate_start_search THEN 1
					ELSE 0 END
			AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
					WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
					ELSE 0 END
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			IsRemoved DESC, n1GroupType DESC, dtJoinDate DESC
	END
END
ELSE IF ( @dtJoinDate_start_search IS NOT NULL )
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		dtJoinDate <= @dtJoinDate_start_search
	AND	IsRemoved = @IsRemoved
	AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
			WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
			ELSE 0 END

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
			dbo.TB_User with ( nolock )
		WHERE
			dtJoinDate <= @dtJoinDate_start_search
		AND	IsRemoved = @IsRemoved
		AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
				WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
				ELSE 0 END
		ORDER BY
			IsRemoved DESC, dtJoinDate DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate
			,	ROW_NUMBER() OVER ( ORDER BY
				IsRemoved DESC, dtJoinDate DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				dtJoinDate <= @dtJoinDate_start_search
			AND	IsRemoved = @IsRemoved
			AND	1 = CASE WHEN @dtJoinDate_end_search IS NULL THEN 1
					WHEN dtJoinDate >= @dtJoinDate_end_search THEN 1
					ELSE 0 END
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			IsRemoved DESC, dtJoinDate DESC
	END
END
ELSE IF ( @dtJoinDate_end_search IS NOT NULL )
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		IsRemoved = @IsRemoved
	AND	dtJoinDate >= @dtJoinDate_end_search
	
	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
			dbo.TB_User with ( nolock )
		WHERE
			IsRemoved = @IsRemoved
		AND	dtJoinDate >= @dtJoinDate_end_search
		ORDER BY
			IsRemoved DESC, dtJoinDate DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate
			,	ROW_NUMBER() OVER ( ORDER BY
				IsRemoved DESC, dtJoinDate DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				IsRemoved = @IsRemoved
			AND	dtJoinDate >= @dtJoinDate_end_search 
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			IsRemoved DESC, dtJoinDate DESC
	END
END
ELSE
BEGIN
	SELECT
		@CntTotal = Count( * )
	FROM
		dbo.TB_User with ( nolock )
	WHERE
		IsRemoved = @IsRemoved

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
			dbo.TB_User with ( nolock )
		WHERE
			IsRemoved = @IsRemoved
		ORDER BY
			IsRemoved DESC, dtJoinDate DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			strUserID
		,	n4UserNo
		,	IsLocalUser
		,	strMD5PWD
		,	strUserName
		,	strPersonalNo
		,	strEmail
		,	n1Question
		,	strAnswer
		,	strPostNo
		,	strAddress1
		,	strAddress2
		,	strHomePhone
		,	strMobilePhone
		,	strBirthday
		,	IsLunar
		,	IsSMS
		,	IsEmail
		,	n1JoinMotiveType
		,	n1UserLevel
		,	n1GroupType
		,	dtJoinDate
		,	dtLastLogin
		,	strLoginIP
		,	IsRemoved
		,	dtRemoveDate
		,	CodeSex
		,	dtModifyDate
		FROM
		(
			SELECT
				strUserID
			,	n4UserNo
			,	IsLocalUser
			,	strMD5PWD
			,	strUserName
			,	strPersonalNo
			,	strEmail
			,	n1Question
			,	strAnswer
			,	strPostNo
			,	strAddress1
			,	strAddress2
			,	strHomePhone
			,	strMobilePhone
			,	strBirthday
			,	IsLunar
			,	IsSMS
			,	IsEmail
			,	n1JoinMotiveType
			,	n1UserLevel
			,	n1GroupType
			,	dtJoinDate
			,	dtLastLogin
			,	strLoginIP
			,	IsRemoved
			,	dtRemoveDate
			,	CodeSex
			,	dtModifyDate
			,	ROW_NUMBER() OVER ( ORDER BY
				IsRemoved DESC, dtJoinDate DESC
			) AS ROWNUM
			FROM
				dbo.TB_User with ( nolock )
			WHERE
				IsRemoved = @IsRemoved
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			IsRemoved DESC, dtJoinDate DESC
	END
END


-- Frk Error Check
SELECT
	@FrkErrorCode = @@ERROR
,	@FrkRowCount = @@RowCount

SET @CntRow = @FrkRowCount

-- End

COMPLETE:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Commit Tran
		End
	End
	SET @ErrorCode = 0
	SET @ErrorMessage = ''
	return 0
ERROR:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Rollback Tran
		End
	End
	SET @ErrorCode = @FrkErrorCode
	SET @ErrorMessage = @FrkErrorMessage
	return @FrkErrorCode
